import time
import pymysql


def get_time():
    time_str = time.strftime('%Y{}%m{}%d{} %X')
    return time_str.format('年', '月', '日')


def get_conn():
    """
    连接MySQL
    :return:
    """
    conn = pymysql.connect(host="***",  # 本地ip或你的域名地址
                           user="***",  # MySQL登录用户名
                           password="***",  # MySQL登录密码
                           db="***",  # 储存数据的数据库名
                           charset="utf8")
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    """
    关闭MySQL
    :param conn:
    :param cursor:
    :return:
    """
    if cursor:
        cursor.close()
    if conn:
        conn.close()


def query(sql, *args):
    """
    通用查询
    :param sql:
    :param args:
    :return:
    """
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res


def get_c1_data():
    """
    :return: 返回累计确诊、剩余疑似、累计治愈、累计死亡数据
    """
    sql = 'select confirm_total, suspect_total, heal_total, dead_total from total ' \
          'where update_time=(select update_time from total order by update_time desc limit 1)'
    res = query(sql)
    return res[0]


def get_c2_data():
    """
    :return: 返回各省数据
    """
    sql = 'select province, sum(confirm) from details ' \
          'where update_time=(select update_time from details order by update_time desc limit 1) ' \
          'group by province'
    res = query(sql)
    return res


def get_l1_data():
    """
    :return: 返回累计确诊、剩余疑似、累计治愈、累计死亡数据
    """
    sql = 'select date, confirm, suspect, heal, dead from history order by date desc limit 60'
    res = query(sql)
    return res


def get_l2_data():
    """
    :return: 返回新增确诊、新增疑似、新增治愈、新增死亡数据
    """
    sql = 'select date, confirm_add, suspect_add, heal_add, dead_add from history order by date desc limit 60'
    res = query(sql)
    return res


def get_r1_data():
    """
    :return: 返回非湖北地区城市确诊人数前10名
    """
    sql = 'select city,confirm from ' \
          '(select city,confirm from details ' \
          'where update_time=(select update_time from details order by update_time desc limit 1) ' \
          'and province not in ("湖北", "上海", "天津", "重庆", "北京", "台湾", "香港") ' \
          'union all ' \
          'select province as city, sum(confirm) as confirm from details ' \
          'where update_time=(select update_time from details order by update_time desc limit 1) ' \
          'and province in ("上海", "天津", "重庆", "北京") group by province) as new_table ' \
          'having city not in ("地区待确认", "境外输入") ' \
          'order by confirm desc limit 10'
    res = query(sql)
    return res


def get_r2_data():
    """
    :return: 返回最近的25条热搜
    """
    sql = 'select content,hot_index from hotsearch order by id desc limit 25'
    res = query(sql)
    return res


if __name__ == '__main__':
    print(get_c1_data())
